package com.wz.jiangsu.main.td;

import org.springframework.util.StopWatch;

import java.sql.*;
import java.util.Random;

public class ParameterBindingBasicDemo3 {

    // modify host to your own
    private static final String host = "10.25.10.250";
//    private static final String host = "127.0.0.1";
    private static final Random random = new Random(System.currentTimeMillis());
    private static final int numOfSubTable = 10000, numOfRow = 1;

    private static final String[] regions = {"east", "west", "north", "south", "center"};

    private static final String[] types = {"plc", "dcs", "ipc", "cnc", "fcs"};

    public static void main(String[] args) throws SQLException {

        String jdbcUrl = "jdbc:TAOS://" + host + ":6030/";
        Connection conn = DriverManager.getConnection(jdbcUrl, "root", "taosdata");

        init(conn);

        String sql = "INSERT INTO ? USING devices TAGS(?,?,?) VALUES (?,?,?,?,?,?)";

        try (Statement stmt = conn.createStatement()) {

            StopWatch stopWatch = new StopWatch();

            stopWatch.start("全量明细数据查询耗时（select *）");
            stmt.executeQuery("SELECT * FROM test.devices");
            stopWatch.stop();


            stopWatch.start("全量数据汇总查询耗时（count(*)）");
            ResultSet rs = stmt.executeQuery("SELECT count(*) FROM test.devices");
            if (rs.next()){
                Integer count = rs.getInt(1);
                System.out.println("count = " + count);
                stopWatch.stop();
            }


            stopWatch.start("最大值查询耗时（max()）");
            ResultSet rsMax = stmt.executeQuery("SELECT max(disk) FROM test.devices");

            if (rsMax.next()){
                Float max = rsMax.getFloat(1);
                System.out.println("max = " + max);
                stopWatch.stop();
            }


            stopWatch.start("最小值查询耗时（min()）");
            ResultSet rsMin = stmt.executeQuery("SELECT min(disk) FROM test.devices");
            if (rsMin.next()){
                Float min = rsMin.getFloat(1);
                System.out.println("min = " + min);
                stopWatch.stop();
            }

            stopWatch.start("总值查询耗时（sum()）");
            ResultSet resultSet1 = stmt.executeQuery("SELECT sum(disk) FROM test.devices");
            if (resultSet1.next()){
                Float sum = resultSet1.getFloat(1);
                System.out.println("sum = " + sum);
                stopWatch.stop();
            }

            stopWatch.start("平均值查询耗时（avg()）");
            ResultSet resultSet2 = stmt.executeQuery("SELECT avg(disk) FROM test.devices");
            if (resultSet2.next()){
                Float avg = resultSet2.getFloat(1);
                System.out.println("avg = " + avg);
                stopWatch.stop();
            }

            stopWatch.start("host为host1的记录明细查询耗时（select * ... where host = 'host1'）");
            stmt.executeQuery("SELECT * FROM test.devices where host_ip ='host1' ");
            stopWatch.stop();


            stopWatch.start("host为host1的记录汇总查询耗时（select count(*) ... where host = 'host1'）");
            ResultSet resultSet3 = stmt.executeQuery("SELECT count(*) FROM test.devices where host_ip ='host1' ");
            if (resultSet3.next()){
                Integer count = resultSet3.getInt(1);
                System.out.println("count = " + count);
                stopWatch.stop();
            }

            stopWatch.start("host为host1的记录最大值查询耗时（max() where host = 'host1'）");
            ResultSet resultSet4 = stmt.executeQuery("SELECT max(disk) FROM test.devices where host_ip ='host1' ");
            if (resultSet4.next()){
                Float max = resultSet4.getFloat(1);
                System.out.println("max = " + max);
                stopWatch.stop();
            }

            stopWatch.start("host为host1的记录最小值查询耗时（min() where host = 'host1'）");
            ResultSet resultSet5 = stmt.executeQuery("SELECT min(disk) FROM test.devices where host_ip ='host1' ");
            if (resultSet5.next()){
                Float min = resultSet5.getFloat(1);
                System.out.println("min = " + min);
                stopWatch.stop();
            }

            stopWatch.start("host为host1的记录总值查询耗时（sum() where host = 'host1'）");
            ResultSet resultSet = stmt.executeQuery("SELECT sum(disk) FROM test.devices where host_ip ='host1' ");
            if (resultSet.next()){
                Float sum = resultSet.getFloat(1);
                System.out.println("sum = " + sum);
                stopWatch.stop();
            }

            stopWatch.start("host为host1的记录平均值查询耗时（avg () where host = 'host1'）");
            ResultSet resultSet6 = stmt.executeQuery("SELECT avg(disk) FROM test.devices where host_ip ='host1' ");
            if (resultSet6.next()){
                Float avg = resultSet6.getFloat(1);
                System.out.println("avg = " + avg);
                stopWatch.stop();
            }

            System.out.println("stopWatch = " + stopWatch.prettyPrint());

        }




        conn.close();
    }

    private static void init(Connection conn) throws SQLException {
        try (Statement stmt = conn.createStatement()) {
            stmt.execute("CREATE DATABASE IF NOT EXISTS test");
            stmt.execute("USE test");
            stmt.execute("CREATE STABLE IF NOT EXISTS devices (ts TIMESTAMP, cpu FLOAT, memory FLOAT, disk FLOAT, network FLOAT, processes INT) TAGS (host_ip BINARY(10), region BINARY(10), type BINARY(10))");
        }
    }
}
